package com.bindatax.core.util;

import com.bindatax.core.model.ValueMetaData;
import org.pentaho.di.core.Const;
import org.pentaho.di.core.database.BaseDatabaseMeta;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.row.ValueMeta;
import org.pentaho.di.core.row.ValueMetaInterface;

import java.io.BufferedReader;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;

public class DatabaseUtil {

    private final static ThreadLocal<Connection> CONN_THREAD_LOCAL = new ThreadLocal<>();
    public static void setConn(Connection conn){
        CONN_THREAD_LOCAL.set(conn);
    }

    public static List<ValueMetaData> getValueFromSQLTypeList(String databaseID, ResultSetMetaData rm) throws Exception {
        int nrcols = rm.getColumnCount();
        List<ValueMetaData> metaList = new ArrayList<ValueMetaData>(nrcols);
        for (int i = 1; i <= nrcols; i++) {
            String name = rm.getColumnName(i);
            ValueMetaInterface vm = getValueFromSQLType(databaseID, name, rm, i, false, false);
            ValueMetaData data = new ValueMetaData();
            data.setValueMetaInterface(vm);
            data.setCloIndex(i);
            metaList.add(data);
        }
        return metaList;
    }

    public static ValueMetaInterface getValueFromSQLType(String databaseID, String name, ResultSetMetaData rm,
                                                         int index, boolean ignoreLength, boolean lazyConversion) throws Exception {
        int length = -1;
        int precision = -1;
        String conversionMask = null;
        int valtype = ValueMetaInterface.TYPE_NONE;
        int orgtype = ValueMetaInterface.TYPE_NONE;
        boolean isClob = false;
        //String databaseID = databaseMeta.getDatabaseInterface().getPluginId().toUpperCase();

        int type = rm.getColumnType(index);
        String sqlType = rm.getColumnTypeName(index);

        // get & store more result set meta data for later use
        int originalColumnType = rm.getColumnType(index);
        String originalColumnTypeName = rm.getColumnTypeName(index);

        boolean signed = false;
        try {
            //有些数据库不支持这个属性，会直接抛异常，所以这里默认为false，捕捉异常
            signed = rm.isSigned(index);
        } catch (SQLException e) {
        }
        switch (type) {
            case Types.TIME_WITH_TIMEZONE:
            case Types.TIMESTAMP_WITH_TIMEZONE:
            case Types.OTHER: {
                // 这是默认使用字符串获取
                valtype = ValueMetaInterface.TYPE_STRING;
                precision = rm.getScale(index);
                break;
            }
            case java.sql.Types.CHAR:
            case java.sql.Types.VARCHAR:
            case java.sql.Types.LONGVARCHAR: // Character Large Object
                valtype = ValueMetaInterface.TYPE_STRING;
                if (!ignoreLength) {
                    length = rm.getColumnDisplaySize(index);
                }
//			gengjie 2012-9-10 长度为0，无法输出到本地文件，所以将长度设置为-1，不限制长度
                if (length == 0) {
                    length = -1;
                }
                break;

            case java.sql.Types.CLOB:
                valtype = ValueMetaInterface.TYPE_STRING;
                length = DatabaseMeta.CLOB_LENGTH;
                isClob = true;
                break;

            case java.sql.Types.BIGINT:
                // verify Unsigned BIGINT overflow!
                //
                if (signed) {
                    valtype = ValueMetaInterface.TYPE_INTEGER;
                    precision = 0; // Max 9.223.372.036.854.775.807
                    length = 19;//修改最大长度为19
                } else {
                    valtype = ValueMetaInterface.TYPE_BIGNUMBER;
                    precision = 0; // Max 18.446.744.073.709.551.615
                    length = 20;//修改最大长度为20
                }
                break;
            // TODO: 2022/7/6 这里有个认知问题，mysql的length是显示宽度的，建表若设置 zerofill (0填充)，即使设置为11位长度，只要不超int的最大值，那么会在数字前面补充0。若没有设置0填充，那么直接显示，
            //      所以这里重置为10的长度不合理，后续需要修改逻辑 by gn
            case java.sql.Types.INTEGER:
                valtype = ValueMetaInterface.TYPE_INTEGER;
                precision = 0; // Max 2.147.483.647
                length = 10;//修改最大长度为10
                break;

            case java.sql.Types.SMALLINT:
                valtype = ValueMetaInterface.TYPE_INTEGER;
                precision = 0; // Max 32.767
                length = 5;//修改最大长度为5
                break;

            case java.sql.Types.TINYINT:
                valtype = ValueMetaInterface.TYPE_INTEGER;
                precision = 0; // Max 127
                length = 3;//修改最大长度为3
                break;

            case java.sql.Types.DECIMAL:
            case java.sql.Types.DOUBLE:
            case java.sql.Types.FLOAT:
            case java.sql.Types.REAL:
            case java.sql.Types.NUMERIC:
                valtype = ValueMetaInterface.TYPE_NUMBER;
                length = rm.getPrecision(index);
                precision = rm.getScale(index);
                if (length >= 126) {
                    length = -1;
                }
                if (precision >= 126) {
                    precision = -1;
                }
                //增加decimal类型
                if (type == java.sql.Types.DECIMAL || type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT || type == java.sql.Types.REAL) {
                    if (precision == 0) {
                        precision = -1; // precision is obviously incorrect if the type if Double/Float/Real
                    }

                    // If we're dealing with PostgreSQL and double precision types
                    if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_POSTGRESQL_PLUGINID)
                            && type == java.sql.Types.DOUBLE && precision >= 16 && length >= 16) {
                        precision = -1;
                        length = -1;
                    }
                    //修改highgo数据库的double precision类型的格式
                    if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_HIGHGO_PLUGINID)
                            && type == java.sql.Types.DOUBLE && precision >= 16 && length >= 16) {
                        precision = 15;
                        conversionMask = "#.############;-#.############";
                    }

                    // MySQL: max resolution is double precision floating point (double)
                    // The (12,31) that is given back is not correct
                    //修改bug TONGETL-91 modify by liyin 2016-03-28 判断当数据类型为decimal、double、float、real时，mysql数据库的掩码为#.############;-#.############，以防止减少精度的情况
                    if (databaseID.startsWith(BaseDatabaseMeta.DATABASE_MYSQL_PLUGINID)) {
                        conversionMask = "#.############;-#.############";
                        if (precision >= length) {
                            precision = -1;
                            length = -1;
                        }
                    }
                    if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_POSTGRESXC_PLUGINID)) {
                        conversionMask = "#.############;-#.############";
                        if (precision >= length) {
                            precision = -1;
                            length = -1;
                        }
                    }
                    // add by liuxu 2014-01-02   当DB2的数据类型是double时，将数据长度设置为-1，使得ETL--DB2；DB2--ETL的数据类型能相互对应。
                    if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_DB2_PLUGINID)
                            && type == java.sql.Types.DOUBLE) {
                        precision = -1;
                        length = -1;
                    }

                    // if the length or precision needs a BIGNUMBER
                    if (length > 15 || precision > 15) {
                        valtype = ValueMetaInterface.TYPE_BIGNUMBER;
                    }
                } else {
                    valtype = ValueMetaInterface.TYPE_BIGNUMBER;

                    /*if (precision == 0) {
                        if (length <= 18 && length > 0) { // Among others Oracle is affected here.
                            valtype = ValueMetaInterface.TYPE_INTEGER; // Long can hold up to 18 significant digits
                        } else if (length > 18) {
                            valtype = ValueMetaInterface.TYPE_BIGNUMBER;
                        }
                    } else { // we have a precision: keep NUMBER or change to BIGNUMBER?
                        if (length > 15 || precision > 15) {
                            valtype = ValueMetaInterface.TYPE_BIGNUMBER;
                        }

                        //Microsoft SQLServer的数据类型smallmoney的精度是(10,4)但也应该按照BIGNUMBER处理。 added by zhangxd 2012.5.16
                        if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_MSSQL_PLUGINID)
                                && type == java.sql.Types.DECIMAL
                                && sqlType.equalsIgnoreCase("smallmoney")) {
                            valtype = ValueMetaInterface.TYPE_BIGNUMBER;
                        }
                    }*/

                }

                if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_POSTGRESQL_PLUGINID)
                        || databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_GREENPLUM_PLUGINID)) {
                    // undefined size => arbitrary precision
                    if (type == java.sql.Types.NUMERIC && length == 0 && precision == 0) {
                        valtype = ValueMetaInterface.TYPE_BIGNUMBER;
                        length = -1;
                        precision = -1;
                    }
                }
                //去掉当oracle数据类型为number(38)时，TIETL内部类型为integer add by liuxu
                if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_ORACLE_PLUGINID)) {
//				if (precision == 0 && length == 38) {
//					valtype = ValueMetaInterface.TYPE_INTEGER;
//				}
                    if (precision <= 0 && length <= 0) // undefined size: BIGNUMBER, precision on Oracle can be 38, too big
                    // for a Number type
                    {
                        valtype = ValueMetaInterface.TYPE_BIGNUMBER;
                        length = -1;
                        precision = -1;
                    }
                }
                break;
            case java.sql.Types.TIMESTAMP:
                length = rm.getScale(index);
                valtype = ValueMetaInterface.TYPE_DATE;
                if (databaseID.startsWith(BaseDatabaseMeta.DATABASE_MYSQL_PLUGINID)) {
                    if (rm.getColumnTypeName(index).equalsIgnoreCase("YEAR")) {
                        valtype = ValueMetaInterface.TYPE_INTEGER;
                        precision = 0;
                        length = 4;
                        break;
                    }
                } else if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_SYBASE_PLUGINID)) {
                    //			gengjie 2012-4-25 对于Sybase数据库的Date和Time类型，额外设置转换掩码
                    if (type == java.sql.Types.DATE) {
                        conversionMask = "yyyy/MM/dd";
                    } else if (type == java.sql.Types.TIME) {
                        conversionMask = "HH:mm:ss:SSS";
                    }
                }
                break;
            case java.sql.Types.DATE:
//			BEGIN: gengjie 2012-4-16 当数据源类型为Sybase时，也将精度设为1，以便之后取纪录时调用可以调用正确的get方法
                if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_TERADATA_PLUGINID)
                        || databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_SYBASE_PLUGINID)) {
                    precision = 1;
                }
//			END: gengjie 2012-4-16
            case java.sql.Types.TIME:
//			gengjie oracle timestamp with (local) timezone
            case -101:
            case -102:
//			gengjie sqlserver datetimeoffset
            case -155:
                valtype = ValueMetaInterface.TYPE_DATE;
                //
                if (databaseID.startsWith(BaseDatabaseMeta.DATABASE_MYSQL_PLUGINID)) {
                    if (rm.getColumnTypeName(index).equalsIgnoreCase("YEAR")) {
                        valtype = ValueMetaInterface.TYPE_INTEGER;
                        precision = 0;
                        length = 4;
                        break;
                    }
                } //			gengjie 2012-4-25 对于Sybase数据库的Date和Time类型，额外设置转换掩码
                else if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_SYBASE_PLUGINID)) {
                    if (type == java.sql.Types.DATE) {
                        conversionMask = "yyyy/MM/dd";
                    } else if (type == java.sql.Types.TIME) {
                        conversionMask = "HH:mm:ss:SSS";
                    }
                } else if (databaseID.startsWith("xugu")) {
                    //对接虚谷数据库时发现Time类型未适配，由于不知道其他数据库Time类型格式是什么暂时默认都格式化为统一格式，后续有问题再加类型判断 add by guning
                    //valtype = ValueMetaInterface.TYPE_TIME;
                    conversionMask = "HH:mm:ss";
                    break;
                }
                break;
//			gengjie 2012-4-26 当类型为BIT的时候，将其长度设为1，使其可以正确显示
            case java.sql.Types.BIT:
            case java.sql.Types.BOOLEAN:
                if (databaseID.startsWith(BaseDatabaseMeta.DATABASE_MYSQL_PLUGINID)
                        && "TINYINT".equalsIgnoreCase(sqlType)) { // fix 	TIETL-1693
                    length = 3;
                    valtype = ValueMetaInterface.TYPE_INTEGER;
                } else if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_MSSQL_PLUGINID)
                        && "bit".equalsIgnoreCase(sqlType)) {//设置sqlserver的bit类型的长度和类型 add by liuxu TONGETL-191
                    length = 1;
                    valtype = ValueMetaInterface.TYPE_BIT;
                } else {
                    valtype = ValueMetaInterface.TYPE_BOOLEAN;
                }
                break;

            case java.sql.Types.BINARY:
            case java.sql.Types.BLOB:
            case java.sql.Types.VARBINARY:
            case java.sql.Types.LONGVARBINARY:

                if ((2 * rm.getPrecision(index)) == rm.getColumnDisplaySize(index)) {
                    // set the length for "CHAR(X) FOR BIT DATA"
                    length = rm.getPrecision(index);
//			gengjie 2012-4-26 只保留oracle的raw和longraw的逻辑，mysql中varbinary（tinyblob）和longvarbinary（blob，longblob）不在这里处理
                } //			gengjie 2012-7-16 去掉对RAW，LONG RAW的特殊处理，统一使用转换为内部类型TYPE_BINARY
                //			else if ((databaseMeta.getDatabaseInterface() instanceof OracleDatabaseMeta)
                //					&& (type == java.sql.Types.VARBINARY || type == java.sql.Types.LONGVARBINARY)) {
                //				// set the length for Oracle "RAW" or "LONGRAW" data types
                //				valtype = ValueMetaInterface.TYPE_STRING;
                //				orgtype = ValueMetaInterface.TYPE_BINARY;
                //				length = rm.getColumnDisplaySize(index);
                //			}
                else {
                    length = -1;
                }
                precision = -1;
                //hive2的binary类型对应为string类型
                if (databaseID.equalsIgnoreCase(BaseDatabaseMeta.DATABASE_HIVE2_PLUGINID)) {
                    valtype = ValueMetaInterface.TYPE_STRING;
                } else {
                    valtype = ValueMetaInterface.TYPE_BINARY;
                }
                break;

            default:
                valtype = ValueMetaInterface.TYPE_STRING;
                precision = rm.getScale(index);
                break;
        }

        // Grab the comment as a description to the field as well.
        String comments = rm.getColumnLabel(index);

        int originalPrecision = -1;
        if (!ignoreLength) {
            rm.getPrecision(index); // Throws exception on MySQL
        }
        int originalScale = rm.getScale(index);
        // boolean originalAutoIncrement=rm.isAutoIncrement(index); DISABLED FOR PERFORMANCE REASONS : PDI-1788
        // int originalNullable=rm.isNullable(index); DISABLED FOR PERFORMANCE REASONS : PDI-1788
        boolean originalSigned = false;
        try {
            originalSigned = rm.isSigned(index);
        } catch (SQLException e) {
        }

        ValueMetaInterface v = new ValueMeta(name, valtype);

        if (conversionMask != null) {
            v.setConversionMask(conversionMask);
        }
        v.setLength(length);
        v.setPrecision(precision);
        v.setComments(comments);
        v.setLargeTextField(isClob);
        v.setOriginalColumnType(originalColumnType);
        v.setOriginalColumnTypeName(originalColumnTypeName);
        v.setOriginalPrecision(originalPrecision);
        v.setOriginalScale(originalScale);
        // v.setOriginalAutoIncrement(originalAutoIncrement); DISABLED FOR PERFORMANCE REASONS : PDI-1788
        // v.setOriginalNullable(originalNullable); DISABLED FOR PERFORMANCE REASONS : PDI-1788
        v.setOriginalSigned(originalSigned);
        // add by chencheng
        v.setSqlTypeDesc(sqlType);

        // See if we need to enable lazy conversion...
        //
        if (lazyConversion && //延迟转换
                orgtype == ValueMetaInterface.TYPE_BINARY && //原来的类型是二进制
                valtype == ValueMetaInterface.TYPE_STRING) { //修改后的类型是字符串
            v.setStorageType(ValueMetaInterface.STORAGE_TYPE_BINARY_STRING);
            // TODO set some encoding to go with this.

            // Also set the storage metadata. a copy of the parent, set to String too.
            //
            ValueMetaInterface storageMetaData = v.clone();
            storageMetaData.setType(ValueMetaInterface.TYPE_STRING);
            storageMetaData.setStorageType(ValueMetaInterface.STORAGE_TYPE_NORMAL);
            v.setStorageMetadata(storageMetaData);
        }
        return v;
    }

    public static void setValue(PreparedStatement ps, ValueMetaInterface v, Object object, int pos)
            throws Exception {
        String debug = "";
        try {
            switch (v.getType()) {
                case ValueMetaInterface.TYPE_NUMBER:
                    if (!v.isNull(object)) {
                        debug = "Number, not null, getting number from value";
                        double num;
                        if("NUMBER".equalsIgnoreCase(v.getSqlTypeDesc())){
                            BigDecimal bigDecimal = (BigDecimal)object;
                            num = bigDecimal.doubleValue();
                        }else {
                            num = v.getNumber(object).doubleValue();
                        }
                        /*if(Types.FLOAT == v.getOriginalColumnType() && BaseDatabaseMeta.ETL_NUMBER_FLOAT.equalsIgnoreCase(v.getOriginalColumnTypeName())){
                            num = Const.round(num, v.getPrecision());
                        }else */
                        //if (databaseMeta.supportsFloatRoundingOnUpdate() && v.getPrecision() >= 0) {
                        if (v.getPrecision() >= 0) {
                            debug = "Number, rounding to precision [" + v.getPrecision() + "]";
                            num = Const.round(num, v.getPrecision());
                        }
                        debug = "Number, setting [" + num + "] on position #" + pos + " of the prepared statement";
                        ps.setDouble(pos, num);
                    } else {
                        ps.setNull(pos, java.sql.Types.DOUBLE);
                    }
                    break;
                case ValueMetaInterface.TYPE_BIT:
                    if (!v.isNull(object)) {
                        debug = "Bit string, not null, getting bit string from value";
                        boolean val = v.getBoolean(object);
                        /*if (databaseMeta.supportBitStringSetBoolean()) {
                            ps.setBoolean(pos, val);
                        } else {
                            ps.setString(pos,v.getBit(object));
                        }*/
                        ps.setBoolean(pos, val);
                    } else {
                        ps.setNull(pos, Types.BIT);
                    }
                    break;
                case ValueMetaInterface.TYPE_INTEGER:
                    debug = "Integer";
                    if (!v.isNull(object)) {
                        ps.setLong(pos, v.getInteger(object).longValue());
                    } else {
                        ps.setNull(pos, java.sql.Types.INTEGER);
                    }
                    break;
                case ValueMetaInterface.TYPE_STRING:
                    debug = "String";
                    if (v.getLength() < DatabaseMeta.CLOB_LENGTH) {
                        if (!v.isNull(object, true)) {
                            ps.setString(pos, v.getString(object));
                        } else {
                            ps.setNull(pos, java.sql.Types.VARCHAR);
                        }
                    } else {
                        if (!v.isNull(object, true)) {
                            // 暂时处理oracle的clob
                            String string = v.getString(object);
                            Connection connection = CONN_THREAD_LOCAL.get();
                            Clob clob = connection.createClob();
                            clob.setString(1,string);
                            ps.setClob(pos , clob );

                            /*int maxlen = DatabaseMeta.CLOB_LENGTH;
                            int len = string.length();
                            // Take the last maxlen characters of the string...
                            int begin = len - maxlen;
                            if (begin < 0) {
                                begin = 0;
                            }
                            // Get the substring!
                            String logging = string.substring(begin);
                            StringReader sr = new StringReader(logging);
                            if (ValueMetaInterface.GEODB_TEXT.equalsIgnoreCase(v.getComments())
                                    || ValueMetaInterface.ORACLE_TEXT.equalsIgnoreCase(v.getComments())) {
                                ps.setClob(pos, sr, logging.length());
                            } else {
                                ps.setCharacterStream(pos, sr, logging.length());
                            }*/

                        } else {
                            ps.setNull(pos, java.sql.Types.VARCHAR);
                        }
                    }
                    break;
                case ValueMetaInterface.TYPE_DATE:
                    debug = "Date";
                    if (!v.isNull(object)) {
                        if("TIMESTAMP".equalsIgnoreCase(v.getSqlTypeDesc())){
                            Timestamp timestamp = Timestamp.valueOf(object.toString());
                            ps.setTimestamp(pos, timestamp);
                        }else if (object instanceof LocalTime) {
                            String str = object.toString();
                            if (str.contains(".")) {
                                str = str.substring(0, str.indexOf("."));
                            }
                            // 检查字符串是否包含冒号 需要的格式为时:分:秒 eg: 12:10:13,如果不是则根据不同数据进行不同处理
                            if (!str.contains(":")) {
                                // 字符串不包含冒号，直接返回原字符串或进行其他处理
                                str = str + ":00:00";
                            } else if (str.indexOf(str.indexOf(":") + 1) == -1) {//根据第一个冒号位置查找是否有第二个冒号
                                str = str + ":00";
                            }
                            java.sql.Time stime = java.sql.Time.valueOf(str);
                            ps.setTime(pos, stime);
                        } else {
                            long dat = v.getInteger(object).longValue(); // converts using Date.getTime()
                            java.sql.Timestamp sdate = new java.sql.Timestamp(dat);
                            ps.setTimestamp(pos, sdate);
                        }
                    } else {
                        ps.setNull(pos, java.sql.Types.TIMESTAMP);
                    }
                    break;
                case ValueMetaInterface.TYPE_TIMESTAMP:
                    if (!v.isNull(object)) {
                        debug = "TIMESTAMP not null, getting bit string from value";
                        Timestamp val = v.getTimestamp(object);
                        ps.setTimestamp(pos, val);
                    } else {
                        ps.setNull(pos, Types.TIMESTAMP);
                    }
                    break;
                /*case ValueMetaInterface.TYPE_TIME:
                    if (!v.isNull(object)) {
                        debug = "TIME not null, getting bit string from value";
                        // Convert to Time!
                        long dat = v.getInteger(object).longValue();
                        java.sql.Time time = new java.sql.Time(dat);
                        ps.setTime(pos, time);
                    } else {
                        ps.setNull(pos, Types.TIME);
                    }
                    break;*/
                case ValueMetaInterface.TYPE_BOOLEAN:
                    debug = "Boolean";
                    if (!v.isNull(object)) {
                        ps.setBoolean(pos, v.getBoolean(object).booleanValue());
                    } else {
                        ps.setNull(pos, java.sql.Types.BOOLEAN);
                    }
                    break;
                case ValueMetaInterface.TYPE_BIGNUMBER:
                    debug = "BigNumber";
                    if (!v.isNull(object)) {
                        ps.setBigDecimal(pos, v.getBigNumber(object));
                    } else {
                        ps.setNull(pos, java.sql.Types.DECIMAL);
                    }
                    break;
                case ValueMetaInterface.TYPE_BINARY:
                    debug = "Binary";
                    if (!v.isNull(object)) {
                        ps.setBytes(pos, v.getBinary(object));
                    } else {
                        ps.setNull(pos, java.sql.Types.BINARY);
                    }
                    break;
                default:
                    debug = "default";
                    // placeholder
                    ps.setNull(pos, java.sql.Types.VARCHAR);
                    break;
            }
        } catch (SQLException ex) {
            throw new Exception("Error setting value #" + pos + " [" + v
                    + "] on prepared statement (" + debug + ")" + Const.CR + ex, ex);
        } catch (Exception e) {
            throw new Exception("Error setting value #" + pos + " ["
                    + (v == null ? "NULL" : v.toString()) + "] on prepared statement (" + debug + ")" + Const.CR
                    + e, e);
        }
    }


    /*
     * 将CLOB类型转成String进行解析
     * */
    public static String oracleClob2Str(Clob clob)throws Exception  {
        return (clob == null || clob.length() == 0) ? null : clob.getSubString(1, (int) clob.length());
    }

    public static String clob2Str(NClob nclob) throws Exception {
        String content = "";
        Reader is = nclob.getCharacterStream();
        BufferedReader buff = new BufferedReader(is);// 得到流
        String line = buff.readLine();
        StringBuffer sb = new StringBuffer();
        while (line != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
            sb.append(line);
            line = buff.readLine();
        }
        content = sb.toString();
        return content;
    }

}
